﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class SystemMenuDAL : ISystemMenuDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public SystemMenuDAL()
        {
            
        }

        public IList<SystemMenuItemListInfo> Search()
        {
            string strSQL;
            IList<SystemMenuItemListInfo> infoList = new List<SystemMenuItemListInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(1, "@username", username);

                //strSQL = "SELECT DISTINCT ml.MENU_ITEM_SEQ"
                //        + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS MENU_ITEM_NAME"
                //        + ", ml.UPPER_MENU_ITEM_SEQ"
                //        + ", ml.MENU_ITEM_PATH"
                //        +", ml.RECORD_STATUS"
                //    + " FROM USR_USER_LIST u"
                //        + " INNER JOIN USR_ROLE_GROUP_LIST gl ON gl.USR_USER_SEQ = u.USR_USER_SEQ"
                //        + " INNER JOIN USR_ROLE_GROUP RG ON gl.USR_ROLE_GRP_SEQ = rg.USR_ROLE_GRP_SEQ"
                //        + " INNER JOIN USR_ROLE_GROUP_ACCESS ga ON rg.USR_ROLE_GRP_SEQ = ga.USR_ROLE_GRP_SEQ"
                //        + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON ga.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                //    + " WHERE u.RECORD_STATUS not like @delete_status"
                //        + " AND gl.RECORD_STATUS not like @delete_status"
                //        + " AND rg.RECORD_STATUS not like @delete_status"
                //        + " AND ga.RECORD_STATUS not like @delete_status"
                //        + " AND ml.RECORD_STATUS not like @delete_status"
                //        + " AND u.USR_USER_SEQ = @username"
                //    + " ORDER BY ml.MENU_ITEM_CODE";


                strSQL = "SELECT ml.BGM_MENU_SEQ"
                        + ", CONCAT('<div class=font-menu>', ml.BGM_MENU_NAME, '</div>') AS MENU_ITEM_NAME"
                        + ", ml.UPPER_BGM_MENU_SEQ"
                        + ", ml.BGM_MENU_PATH"
                        + ", ml.RECORD_STATUS"
                    + " FROM BGM_SYSTEM_MENU ml"
                    + " WHERE ml.RECORD_STATUS not like @delete_status"
                    + " ORDER BY ml.BGM_MENU_CODE";

                DataTable rdr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                //if (rdr.Rows.Count > 0)
                //{
                //    SystemMenuItemListInfo menu = new SystemMenuItemListInfo();

                //    menu.Sequence = rdr.Rows[].GetInt16(0);
                //    menu.Name = rdr.GetString(1);
                //    menu.Parent = (rdr.IsDBNull(2)) ? null : (Nullable<int>)rdr.GetInt16(2);
                //    menu.Path = (rdr.IsDBNull(3)) ? "" : rdr.GetString(3);

                //    infoList.Add(menu);
                //}
                foreach (DataRow dr in rdr.Rows)
                {
                    SystemMenuItemListInfo menu = new SystemMenuItemListInfo();

                    menu.Sequence = Convert.ToInt32(dr["BGM_MENU_SEQ"]);
                    menu.Name = dr["MENU_ITEM_NAME"].ToString();
                    menu.Parent = cn.ConvertNullInt(dr["UPPER_BGM_MENU_SEQ"]);
                    menu.Path = dr["BGM_MENU_PATH"].ToString();

                    infoList.Add(menu);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public DataTable GetMenuItemList(string strUserID)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@strUserID", strUserID);

                strSQL = "SELECT DISTINCT ml.MENU_ITEM_SEQ"
                        + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS MENU_ITEM_NAME"
                        + ", ml.UPPER_MENU_ITEM_SEQ"
                        + ", ml.MENU_ITEM_PATH"
                        + ", ml.MENU_ITEM_TARGET"
                        + ", ml.RECORD_STATUS"
                    + " FROM SYS_SC_USERS u"
                    + " INNER JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                        + " INNER JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = r.ROLE_SEQ"
                        + " INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ"
                        + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                    + " WHERE u.RECORD_STATUS not like @DeleteRecord"
                        + " AND ur.RECORD_STATUS not like @DeleteRecord"
                        + " AND r.RECORD_STATUS not like @DeleteRecord"
                        + " AND p.RECORD_STATUS not like @DeleteRecord"
                        + " AND ml.RECORD_STATUS not like @DeleteRecord"
                        + " AND u.USER_SEQ = @strUserID"
                        + " AND ml.SYSTEM_ID = 'ESS'"
                    + " ORDER BY ml.MENU_ITEM_CODE";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }


        public DataSet LoadMenuByUsername(int username, string system)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            dbManager.AddParameters(1, "@username", username);
            dbManager.AddParameters(2, "@system", system);

            string sql = "SELECT DISTINCT ml.MENU_ITEM_SEQ"
                        + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS MENU_ITEM_NAME"
                        + ", ml.UPPER_MENU_ITEM_SEQ"
                        + ", ml.MENU_ITEM_PATH"
                        + ", ml.MENU_ITEM_TARGET"
                        + ", ml.RECORD_STATUS"
                        + ", r.system"
                    + " FROM SYS_SC_USERS u"
                    + " INNER JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                        + " INNER JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = r.ROLE_SEQ"
                        + " INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ"
                        + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                    + " WHERE u.RECORD_STATUS not like @delete_status"
                        + " AND ur.RECORD_STATUS not like @delete_status"
                        + " AND r.RECORD_STATUS not like @delete_status"
                        + " AND p.RECORD_STATUS not like @delete_status"
                        + " AND ml.RECORD_STATUS not like @delete_status"
                        + " AND u.USER_SEQ = @username"
                        + " AND r.system = @system"
                    + " ORDER BY ml.MENU_ITEM_CODE";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        // It's not the same as GHRMS or other module
        public string GetTitlePage(string pageName)
        {
            string resultTitlePage = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@PageName", pageName);
                string strSQL = @"SELECT bgm_menu_name FROM bgm_system_menu
                                    WHERE LOWER(bgm_menu_path) LIKE CONCAT('%',LOWER(@PageName),'%')
                                        and record_status not like @DeleteRecord ";
                DataTable resultDt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (resultDt.Rows.Count > 0)
                {
                    resultTitlePage = Convert.ToString(resultDt.Rows[0]["bgm_menu_name"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return resultTitlePage;
        }
    }
}
